package com.um.springboot.starter.dao;

import com.um.springboot.starter.orm.entity.BaseEntity;
import com.um.springboot.starter.utils.*;
import lombok.extern.slf4j.Slf4j;

import java.beans.BeanInfo;
import java.beans.PropertyDescriptor;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
public class ColumnUtil {

	public static final String COLUMN_NAMES = "column_names";
	public static final String COLUMN_VALUES = "column_values";
	public static final String ARRAY_VALUES = "array_values";
	public static final String WHERE_NAMES = "where_names";
	public static final String WHERE_VALUES = "where_values";


	private static Map<String,List<String>> mapTable = new Hashtable<String,List<String>>();

	public static String getPrimaryKey(String tableName){
		return tableName+"_id";
	}
	public static String getPrimaryName(String tableName){
		return tableName+"_name";
	}
	public static String getParentKey(String tableName){
		return tableName+"_parent_id";
	}

	/**
	 * 格式化日期
	 * @param obj
	 * @return
	 */
	public static Object dateStrFormat(Object obj){
		if(obj instanceof String){

			//2020-10-19 邓南辉修改，由于2020-10-17T10:57:25.000+08:00转不回来
			//DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");  //yyyy-MM-dd'T'HH:mm:ss.SSSZ
			DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
			//Date  date = df.parse(oldDateStr);
			//SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.000+00:00");
			Date date;
			try {
				date = df.parse(StringUtil.getTrim(obj));
			} catch (ParseException e) {
				return obj;
			}
			log.info("格式化日期： "+StringUtil.getTrim(obj)+" >>> "+date);
			return date;
		}else if(obj instanceof List){
			return JSONUtil.toJSONString(obj);
		}
		return obj;
	}

	public static Map<String,Object> getInsertColumnValues(Object entity){

		if(entity instanceof BaseEntity){

			Map<String, Object> mapReturn = new Hashtable<String, Object>();

			StringBuilder sbColumns = new StringBuilder();
			StringBuilder sbValues = new StringBuilder();
			List<Object> list = new ArrayList<Object>();

			Class<?> clazz = entity.getClass();

			//获取属性信息
			BeanInfo beanInfo = ClassUtils.getSelfBeanInfo(clazz);
			PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();

			sbColumns.append("(");
			sbValues.append("(");

			for(PropertyDescriptor pd : pds){
				//获得属性值
				String name = pd.getName();
				Object value = ReflectUtil.getValue(entity, name);
				if(value == null || "class".equals(name)){
					continue;
				}

				sbColumns.append(NameUtils.getColumnName(name));
				sbValues.append("?");

				sbColumns.append(",");
				sbValues.append(",");

				list.add(dateStrFormat(value));

			}
			sbColumns.deleteCharAt(sbColumns.length()-1);
			sbValues.deleteCharAt(sbValues.length()-1);
			sbColumns.append(")");
			sbValues.append(")");

			mapReturn.put(COLUMN_NAMES, sbColumns);
			mapReturn.put(COLUMN_VALUES, sbValues);
			mapReturn.put(ARRAY_VALUES, list.toArray());
			return mapReturn;
		}else {
			Map map = (Map)entity;
			Map<String, Object> mapReturn = new Hashtable<String, Object>();

			StringBuilder sbColumns = new StringBuilder();
			StringBuilder sbValues = new StringBuilder();
			List<Object> list = new ArrayList<Object>();

			Iterator<String> it = map.keySet().iterator();
			int index = 0;
			if (map != null && map.size() > 0) {
				sbColumns.append("(");
				sbValues.append("(");
				while (it.hasNext()) {
					String property = it.next();
					//不合法的参数名，则忽略
					if(!isSqlPropertyNameValid(property))
						continue;

					if (index > 0) {
						sbColumns.append(",");
						sbValues.append(",");
					}
					sbColumns.append(property);
					sbValues.append("?");
					list.add(dateStrFormat(map.get(property)));
					index++;
				}
				sbColumns.append(")");
				sbValues.append(")");
			}
			mapReturn.put(COLUMN_NAMES, sbColumns);
			mapReturn.put(COLUMN_VALUES, sbValues);
			mapReturn.put(ARRAY_VALUES, list.toArray());
			return mapReturn;
		}
	}

	public static Map<String,Object> getInsertColumnValues(Object entity,String[] properties,String[] values){

		if(entity instanceof  BaseEntity){
			Map<String, Object> mapReturn = new Hashtable<String, Object>();

			StringBuilder sbColumns = new StringBuilder();
			StringBuilder sbValues = new StringBuilder();
			List<Object> list = new ArrayList<Object>();

			Class<?> clazz = entity.getClass();

			//获取属性信息
			BeanInfo beanInfo = ClassUtils.getSelfBeanInfo(clazz);
			PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();

			sbColumns.append("(");
			sbValues.append("(");

			for(PropertyDescriptor pd : pds){
				//获得属性值
				String name = pd.getName();
				Object value = ReflectUtil.getValue(entity, name);
				if(value == null || "class".equals(name)){
					continue;
				}

				sbColumns.append(NameUtils.getColumnName(pd.getName()));
				sbValues.append("?");

				sbColumns.append(",");
				sbValues.append(",");

				list.add(dateStrFormat(value));

			}

			for (int num = 0; num < properties.length; num++) {

				sbColumns.append(properties[num]);
				sbColumns.append(",");


				sbValues.append(values[num]);
				sbValues.append(",");
			}

			sbColumns.deleteCharAt(sbColumns.length()-1);
			sbValues.deleteCharAt(sbValues.length()-1);
			sbColumns.append(")");
			sbValues.append(")");

			mapReturn.put(COLUMN_NAMES, sbColumns);
			mapReturn.put(COLUMN_VALUES, sbValues);
			mapReturn.put(ARRAY_VALUES, list.toArray());
			return mapReturn;
		}else {

			Map map = (Map)entity;
			Map<String, Object> mapReturn = new Hashtable<String, Object>();

			StringBuilder sbColumns = new StringBuilder();
			StringBuilder sbValues = new StringBuilder();
			List<Object> list = new ArrayList<Object>();

			Iterator<String> it = map.keySet().iterator();
			int index = 0;
			if (map != null && map.size() > 0) {
				sbColumns.append("(");
				sbValues.append("(");
				while (it.hasNext()) {
					String property = it.next();
					//不合法的参数名，则忽略
					if(!isSqlPropertyNameValid(property))
						continue;

					if (index > 0) {
						sbColumns.append(",");
						sbValues.append(",");
					}
					sbColumns.append(property);
					sbValues.append("?");
					list.add(dateStrFormat(map.get(property)));
					index++;
				}
				for (int num = 0; num < properties.length; num++) {
					sbColumns.append(",");
					sbColumns.append(properties[num]);

					sbValues.append(",");
					sbValues.append(values[num]);
				}
				sbColumns.append(")");
				sbValues.append(")");
			}
			mapReturn.put(COLUMN_NAMES, sbColumns);
			mapReturn.put(COLUMN_VALUES, sbValues);
			mapReturn.put(ARRAY_VALUES, list.toArray());
			return mapReturn;
		}
	}

	/**
	 *
	 * @param entity
	 * @param oper
	 * @param columns
	 * @param updateNullProperties 这里指null的字段也需要更新
	 * @return
	 */
	public static Map<String,Object> getUpdateColumnValues(Object entity,String oper,String[] columns,String[] updateNullProperties){

		List nullColumns = Arrays.asList(updateNullProperties);
		nullColumns = new ArrayList(nullColumns);

		if(entity instanceof  BaseEntity){

			Map<String, Object> mapReturn = new Hashtable<String, Object>();

			StringBuilder sbColumns = new StringBuilder();
			List<Object> list = new ArrayList<Object>();

			Class<?> clazz = entity.getClass();

			//获取属性信息
			BeanInfo beanInfo = ClassUtils.getSelfBeanInfo(clazz);
			PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();

			for(PropertyDescriptor pd : pds){
				//获得属性值
				String name = pd.getName();
				Object value = ReflectUtil.getValue(entity, name);
				if(nullColumns.indexOf(name) < 0 && (value == null || "class".equals(name))){
					continue;
				}

				//忽略is_delete字段
				if("isDelete".equals(name) || "deleteTime".equals(name)){
					continue;
				}

				sbColumns.append(NameUtils.getColumnName(pd.getName()) + " = ? ");
				sbColumns.append(",");
				list.add(dateStrFormat(value));
			}
			sbColumns.deleteCharAt(sbColumns.length()-1);

			StringBuilder sbWhere = new StringBuilder();
			int len = columns.length;
			if (len > 0)
				sbWhere.append(" where ");

			for (int index = 0; index < len; index ++) {
				if (index > 0)
					sbWhere.append(" " + oper + " ");
				sbWhere.append(columns[index] + " = ? ");

				list.add(dateStrFormat(ReflectUtil.getValue(entity,NameUtils.getPropertyName(columns[index]))));

			}

			mapReturn.put(COLUMN_NAMES, sbColumns);
			mapReturn.put(ARRAY_VALUES, list.toArray());
			mapReturn.put(WHERE_VALUES, sbWhere);
			return mapReturn;

		}else {

			Map map = (Map)entity;
			Map<String, Object> mapReturn = new Hashtable<String, Object>();

			StringBuilder sbColumns = new StringBuilder();
			List<Object> list = new ArrayList<Object>();

			Iterator<String> it = map.keySet().iterator();
			int index = 0;
			if (map != null && map.size() > 0) {
				while (it.hasNext()) {
					String property = it.next();

					//不合法的参数名，则忽略
					if(!isSqlPropertyNameValid(property))
						continue;

					//忽略is_delete字段
					if("is_delete".equals(property) || "delete_time".equals(property)){
						continue;
					}

					if (index > 0) {
						sbColumns.append(",");
					}
					sbColumns.append(property + " = ? ");
					list.add(dateStrFormat(map.get(property)));
					index++;
				}
			}

			StringBuilder sbWhere = new StringBuilder();
			int len = columns.length;
			if (len > 0) sbWhere.append(" where ");
			for (int i = 0; i < len; i++) {
				if (i > 0)
					sbWhere.append(" " + oper + " ");
				sbWhere.append(columns[i] + " = ? ");
				list.add(dateStrFormat(map.get(columns[i])));

			}

			mapReturn.put(COLUMN_NAMES, sbColumns);
			mapReturn.put(ARRAY_VALUES, list.toArray());
			mapReturn.put(WHERE_VALUES, sbWhere);
			return mapReturn;
		}
	}


	public static Map<String,Object> getUpdateColumnValues(Object entity,String oper,String[] properties ,String[] values,String[] columns,String[] updateNullColumns){

		List nullColumns = Arrays.asList(updateNullColumns);
		nullColumns = new ArrayList(nullColumns);

		if(entity instanceof  BaseEntity){
			Map<String, Object> mapReturn = new Hashtable<String, Object>();

			StringBuilder sbColumns = new StringBuilder();
			List<Object> list = new ArrayList<Object>();

			Class<?> clazz = entity.getClass();

			//获取属性信息
			BeanInfo beanInfo = ClassUtils.getSelfBeanInfo(clazz);
			PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();

			for(PropertyDescriptor pd : pds){
				//获得属性值
				String name = pd.getName();
				Object value = ReflectUtil.getValue(entity, name);

				if(nullColumns.indexOf(name) < 0 && (value == null || "class".equals(name))){
					continue;
				}


				//忽略is_delete字段
				if("isDelete".equals(name) || "deleteTime".equals(name)){
					continue;
				}

				sbColumns.append(NameUtils.getColumnName(pd.getName()) + " = ? ");
				sbColumns.append(",");
				list.add(dateStrFormat(value));
			}

			for (int num = 0; num < properties.length; num++) {

				sbColumns.append(properties[num] + "=" + values[num]);
				sbColumns.append(",");
			}

			sbColumns.deleteCharAt(sbColumns.length()-1);

			StringBuilder sbWhere = new StringBuilder();
			int len = columns.length;
			if (len > 0)
				sbWhere.append(" where ");

			for (int index = 0; index < len; index ++) {
				if (index > 0)
					sbWhere.append(" " + oper + " ");
				sbWhere.append(columns[index] + " = ? ");

				list.add(dateStrFormat(ReflectUtil.getValue(entity,NameUtils.getPropertyName(columns[index]))));

			}

			mapReturn.put(COLUMN_NAMES, sbColumns);
			mapReturn.put(ARRAY_VALUES, list.toArray());
			mapReturn.put(WHERE_VALUES, sbWhere);
			return mapReturn;
		}else {

			Map map = (Map)entity;
			Map<String, Object> mapReturn = new Hashtable<String, Object>();

			StringBuilder sbColumns = new StringBuilder();
			List<Object> list = new ArrayList<Object>();

			Iterator<String> it = map.keySet().iterator();
			int index = 0;
			if (map != null && map.size() > 0) {
				while (it.hasNext()) {
					String property = it.next();

					//不合法的参数名，则忽略
					if(!isSqlPropertyNameValid(property))
						continue;

					//忽略is_delete字段
					if("is_delete".equals(property) || "delete_time".equals(property)){
						continue;
					}

					if (index > 0) {
						sbColumns.append(",");
					}
					sbColumns.append(property + " = ? ");
					list.add(dateStrFormat(map.get(property)));
					index++;
				}

				for (int num = 0; num < properties.length; num++) {
					sbColumns.append(",");
					sbColumns.append(properties[num] + "=" + values[num]);
				}
			}

			StringBuilder sbWhere = new StringBuilder();
			int len = columns.length;
			if (len > 0) sbWhere.append(" where ");
			for (int i = 0; i < len; i++) {
				if (i > 0)
					sbWhere.append(" " + oper + " ");
				sbWhere.append(columns[i] + " = ? ");
				list.add(dateStrFormat(map.get(columns[i])));

			}

			mapReturn.put(COLUMN_NAMES, sbColumns);
			mapReturn.put(ARRAY_VALUES, list.toArray());
			mapReturn.put(WHERE_VALUES, sbWhere);
			return mapReturn;
		}
	}

	/**
	 * 转换成查询列名
	 * @param columns
	 * @return
	 */
	public static String getColumns(Object[] columns){
		String columnNames = "";
		for(int index = 0;index < columns.length;index ++){
			if(index > 0)
				columnNames += ",";
			columnNames += columns[index].toString();
		}
		return columnNames;
	}

	public static String getIgnoreColumns(String tableName,Object[] columns){
		List<String> lstColumns = mapTable.get(tableName);
		Map<String,String> map = new Hashtable<String,String>();
		for(Object column : columns)
			map.put(column.toString(), column.toString());
		String columnNames = "";
		int size = lstColumns.size();
		for(int index = 0;index < size;index ++){
			if(!map.containsKey(lstColumns.get(index))){
				if(index > 0)
					columnNames += ",";
				columnNames += columns[index];
			}
		}
		return columnNames;
	}

	/**
	 * 根据Map生成查询条件
	 * @param map
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Map getQueryCondition(Map map){

		String sql = " ";
		List list = new ArrayList();
		Iterator it = map.keySet().iterator();
		while (it.hasNext()) {
			String key = it.next().toString();
			if ((key.equals("start")) || (key.equals("limit")) || (key.equals("orderby")) || key.equals("subTableName"))
				continue;

			//不合法的参数名，则忽略
			if(!isSqlPropertyNameValid(key))
				continue;

			Object value = map.get(key);
			if (StringUtil.getTrim(value).equals("") || (value.equals("%")) || (value.equals("%%")))
				continue;

			if(StringUtil.getTrim(value).contains("%")) {
				sql = sql + " and " + key + " like ? ";
			}else{
				sql = sql + " and " + key + " = ? ";
			}
			list.add(StringUtil.getTrim(value));
		}

		Map mapQuery = new Hashtable();
		mapQuery.put("sql",sql);
		mapQuery.put("list",list);

		return mapQuery;
	}

	/**
	 * 根据custom生成查询条件
	 * @param map
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Map getQueryCustom(Map map){

		String sql = "";
		List<String> sqlList = new ArrayList<>();
		List list = new ArrayList();

		Map mapRange = (Map)map.get("rangeField");

		if(mapRange != null){
			Iterator it = mapRange.keySet().iterator();
			while (it.hasNext()) {
				String name = it.next().toString();
				//不合法的参数名，则忽略
				if(!isSqlPropertyNameValid(name))
					continue;

				List rangeList = (List)mapRange.get(name);
				if(rangeList.size() > 0) {
					String tempCustom = "(";
					String operator = "";
					for (int index = 0; index < rangeList.size(); index++) {
						Map rangeItem = (Map) rangeList.get(index);
						if (!tempCustom.equals("("))
							operator = " and ";

						String value = StringUtil.getTrim(rangeItem.get("value"));

						if (StringUtil.getTrim(rangeItem.get("range")).equals("begin")) {
							tempCustom += operator + " " + name + " >= ?";
							list.add(value);
						} else {
							tempCustom += operator + " " + name + " <= ?";
							list.add(value);
						}
					}
					tempCustom += " ) ";
					sqlList.add(tempCustom);
				}
			}
		}


		Map mapOrList = (Map)map.get("orList");
		if(mapOrList != null){
			Iterator itOrList = mapOrList.keySet().iterator();
			while(itOrList.hasNext()){
				String name = itOrList.next().toString();
				//不合法的参数名，则忽略
				if(!isSqlPropertyNameValid(name))
					continue;

				List orList = (List)mapOrList.get(name);

				if(orList.size() > 0) {
					String tempCustom = "(";
					String operator = "";
					for (int index = 0; index < orList.size(); index++) {
						String value = StringUtil.getTrim(orList.get(index));
						if (!tempCustom.equals("("))
							operator = " or ";

						if (StringUtil.equals(value, "isnull")) {
							tempCustom += operator + " " + name + " is null";
							continue;
						}

						tempCustom += operator + " " + name + " = ?";
						list.add(value);
					}
					tempCustom += " ) ";
					sqlList.add(tempCustom);
				}
			}
		}

		if (sqlList.size() > 0) {
			sql= " and " + String.join(" and ", sqlList);
		}


		Map mapQuery = new Hashtable();
		mapQuery.put("sql",sql);
		mapQuery.put("list",list);
		return mapQuery;
	}


	/**
	 * 根据指定的字段名封装成查询条件的Map
	 * @param map
	 * @param values 字段名数组
	 * @return
	 */
	public static Map parseCondition(Map map,Object... values){
		Map newMap = new Hashtable();
		for(int index = 0;index < values.length;index ++){
			String key = StringUtil.getTrim(values[index]);
			if(map.containsKey(key)){
				newMap.put(key,map.get(key));
			}
		}
		return newMap;
	}

	/**
	 * 封装范围查询的方法
	 * @param custom 自定义的map
	 * @param name 查询属性名称
	 * @param beginField 开始范围值我称
	 * @param endField 截止范围值名称
	 */
	public static void parseRangeField(Map custom,Map map,String name,String beginField,String endField){

		List list = new ArrayList();
		if(map.containsKey(beginField)) {
			Map mapBegin = new Hashtable();
			mapBegin.put("range", "begin");
			mapBegin.put("value", map.get(beginField));
			list.add(mapBegin);
		}

		if(map.containsKey(endField)) {
			Map mapEnd = new Hashtable();
			mapEnd.put("range", "end");
			mapEnd.put("value", map.get(endField));
			list.add(mapEnd);
		}

		Map range = null;
		if(custom.containsKey("rangeField")){
			range = (Map)custom.get("rangeField");
		}else{
			range = new Hashtable();
			custom.put("rangeField",range);
		}

		range.put(name,list);
	}

	private static String reg = "(?:')|(?:--)|(/\\*(?:.|[\\n\\r])*?\\*/)|"
			+ "(\\b(select|update|and|or|delete|insert|trancate|char|into|substr|ascii|declare|exec|count|master|into|drop|execute)\\b)";

	private static Pattern sqlPattern = Pattern.compile(reg, Pattern.CASE_INSENSITIVE);//表示忽略大小写

	/**
	 * 验证参数名是否合法
	 * @param str
	 * @return
	 */
	public static boolean isSqlPropertyNameValid(String str) {
		Matcher matcher = sqlPattern.matcher(str);
		if (matcher.find()) {
			log.warn("参数存在非法字符，请确认：" + matcher.group() + " false" + str);//获取非法字符
			return false;
		} else {
			return true;
		}
	}


}
